

import pymysql

db = pymysql.connect(
    host='localhost',
    user='root',
    password='',
    database='company',
    port=3306,
    charset='utf8'
)
cursor=db.cursor()
# sql="create table person_info(username varchar(20),age int,sex char(4),height double(3,2))"
# cursor.execute(sql)
# db.commit()
#
# cursor.close()
# db.close()
# print(data,type(data))
# for i in data:
#     print(i)
#
#
# n=1
# def c_user():
#     try:
#         username=input("请输入你的名字：")
#         age=int(input("请输入你的年龄："))
#         sex=input("请输入你的性别：")
#         height=float(input("请输入你的身高："))
#         user_info={"user_name":username,"user_age":age,"user_sex":sex,"user_height":height}
#         return user_info
#     except Exception as e:
#         print("输入格式错误，请重新输入！",e)
# while n in range(1,11):
#     try:
#         n+=1
#         new_user=c_user()
#         print("输入信息成功！")
#         sql = "insert into person_info (username,age,sex,height) values(%s,%s,%s,%s)"
#         cursor.execute(sql,(new_user["user_name"],new_user["user_age"],new_user["user_sex"],new_user["user_height"]))
#         db.commit()
#         print("已存入数据库！可以输入下一个人的信息")
#     except Exception as f:
#         print("输入格式错误，请重新输入！",f)
# else:
#     print("已存入10人")




import openpyxl

workbook = openpyxl.Workbook("user.xlsx")
workbook.create_sheet(index=0,title="first sheet")
workbook.save("user.xlsx")

workbook=openpyxl.load_workbook("user.xlsx")
worksheet = workbook["first sheet"]
worksheet["A1"]="id"
worksheet["B1"]="idcard"
worksheet["C1"]="username"
worksheet["D1"]="realname"
worksheet["E1"]="pwd"
worksheet["F1"]="telephone"
worksheet["G1"]="email"
worksheet["H1"]="age"
worksheet["I1"]="sex"
worksheet["J1"]="address"
worksheet["K1"]="hiredate"
worksheet["L1"]="sal"
worksheet["M1"]="job"
worksheet["N1"]="company"
workbook.save("user.xlsx")
for i in range(2,2001):
    worksheet.append(range(1,15))
workbook.save("user.xlsx")
r=worksheet['2':'2000']
rows=worksheet.iter_rows(min_row=2,max_row=2000,min_col=1,max_col=14,values_only=True)


# print(d_list[0])
# sql="CREATE table user_info(id int(11) ,idcard varchar(50) ,username varchar(50) ,realname varchar(50) ,pwd varchar(50) ,telphone varchar(12) ,email varchar(100) ,age int(11) ,sex varchar(20) ,address varchar(200) ,hiredate date ,sal double(9,2) ,job varchar(100) ,company varchar(100))"
# cursor.execute(sql)
# db.commit()
for row in rows:
    sql="insert into user_info(id,idcard,username,realname,pwd,telphone,email,age,sex,address,hiredate,sal,job,company)values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"

    cursor.execute(sql,row)
db.commit()
cursor.close()
db.close()